package com.ejuornal.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import com.ejuornal.bean.Artical;
import com.ejuornal.bean.PerPeriodicalDetail;
import com.ejuornal.dao.PerPeriodicalDetailDao;

@Component("perPeriodicalDetailDao")
public class PerPeriodicalDetailDaoImpl extends DaoSupport<PerPeriodicalDetail>
		implements PerPeriodicalDetailDao {

	public List<Artical> findArtical(int perPeriodicalId, int boardId, String status) {
		String sql = "SELECT * FROM articalperperiodicalview WHERE perPeriodicalId=? AND boardId=? AND astatus=?";
		List<Artical> articals = super.getJdbcTemplate().query(sql,
				new Object[] { perPeriodicalId, boardId, status }, new RowMapper<Artical>() {

					public Artical mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Artical artical = new Artical();
						artical.setArticalId(rs.getInt("articalId"));
						artical.setTitle(rs.getString("atitle"));
						artical.setPubDate(rs.getDate("apubDate"));
						
						return artical;
					}
				});

		return articals;
	}

	public List<PerPeriodicalDetail> findBoardArtical(int perPeriodicalId, String status) {
		String sql = "SELECT Distinct perPeriodicalId, boardId, bname, ppnumber FROM articalperperiodicalview WHERE perPeriodicalId=? AND astatus=?";
		List<PerPeriodicalDetail> perPeriodicalDetails = super.getJdbcTemplate().query(sql, new Object[] { perPeriodicalId, status }, new RowMapper<PerPeriodicalDetail>() {

							public PerPeriodicalDetail mapRow(ResultSet rs, int rowNum) throws SQLException {
								PerPeriodicalDetail perPeriodicalDetail = new PerPeriodicalDetail();
								perPeriodicalDetail.setBoardId(rs.getInt("boardId"));
								perPeriodicalDetail.setBname(rs.getString("bname"));
								perPeriodicalDetail.setPerPeriodicalId(rs.getInt("perPeriodicalId"));
								perPeriodicalDetail.setPpnumber(rs.getInt("ppnumber"));
								
								return perPeriodicalDetail;
							}
						});
		for(int i=0; i<perPeriodicalDetails.size(); i++) {
			int boardId = perPeriodicalDetails.get(i).getBoardId();
			List<Artical> articals = findArtical(perPeriodicalId, boardId, status);
			perPeriodicalDetails.get(i).setArticals(articals);
		}
		
		return perPeriodicalDetails;
	}

	public List<PerPeriodicalDetail> findEditor(int periodicalId) {
		String sql = "SELECT * FROM editorView WHERE periodicalId=?";
		List<PerPeriodicalDetail> perPeriodicalDetails = super
				.getJdbcTemplate().query(sql, new Object[] { periodicalId },
						new RowMapper<PerPeriodicalDetail>() {

							public PerPeriodicalDetail mapRow(ResultSet rs, int rowNum) throws SQLException {
								PerPeriodicalDetail perPeriodicalDetail = new PerPeriodicalDetail();
								perPeriodicalDetail.setEditor(rs.getString("realName"));
								
								return perPeriodicalDetail;
							}
						});

		return perPeriodicalDetails;
	}

	public List<PerPeriodicalDetail> findEditorCommit(int periodicalId) {
		String sql = "SELECT * FROM editorCommitView WHERE periodicalId=?";
		List<PerPeriodicalDetail> perPeriodicalDetails = super
				.getJdbcTemplate().query(sql, new Object[] { periodicalId },
						new RowMapper<PerPeriodicalDetail>() {

							public PerPeriodicalDetail mapRow(ResultSet rs, int rowNum) throws SQLException {
								PerPeriodicalDetail perPeriodicalDetail = new PerPeriodicalDetail();
								perPeriodicalDetail.setEditorCommit(rs.getString("realName"));
								
								return perPeriodicalDetail;
							}
						});

		return perPeriodicalDetails;
	}
}
